package com.longe.poi;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;

/**
 * 简单的excel读取操作
 */
public class thirdSimpleDemo {

    public static void main(String[] args) throws Exception {

        writeExcel();

        FileInputStream fis = new FileInputStream(new File("D:\\TEST_POI\\third.xlsx"));
        Workbook workbook = new XSSFWorkbook(fis);

        Sheet sheet = workbook.getSheet("firstSheet");
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            Row currentRow = sheet.getRow(i);
            for (int j = currentRow.getFirstCellNum(); j < currentRow.getLastCellNum(); j++) {
                Cell currentCell = currentRow.getCell(j);
                switch (currentCell.getCellType()) {
                    case STRING: /**字符类型**/
                        System.err.println(currentCell.getStringCellValue());
                        break;
                    case BOOLEAN: /**布尔类型**/
                        System.err.println(currentCell.getBooleanCellValue());
                        break;
                    case NUMERIC: /** NUMERIC包括了日期和数字类型 **/
                        if (DateUtil.isCellDateFormatted(currentCell)){
                            System.err.println(currentCell.getDateCellValue());
                        } else{
                            System.err.println(currentCell.getNumericCellValue());
                        }
                        break;
                    default: BLANK: /**空**/
                        System.err.println("");
                        break;
                }
            }
        }
    }

    private static void writeExcel() throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("firstSheet");

        Row row = sheet.createRow(0);//第一行
        Cell cell = row.createCell(0);
        cell.setCellValue("字符串类型");//设置string类型的

        cell = row.createCell(1);
        cell.setCellValue(3.14);//设置double类型的

        cell = row.createCell(2);
        cell.setCellValue(false);//设置boolean类型的

        /** 设置日期类单元格格式 **/
        CellStyle cellStyle = workbook.createCellStyle();
        CreationHelper creationHelper = workbook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd hh:mm:ss"));

        cell = row.createCell(3);
        cell.setCellValue(new Date());//设置Date类型的
        cell.setCellStyle(cellStyle);

        cell = row.createCell(4);
        cell.setCellValue(Calendar.getInstance());//设置Calendar类型的
        cell.setCellStyle(cellStyle);

        cell = row.createCell(5);
        cell.setCellValue(setXssfRichTextString(workbook));//设置富文本类型的

        FileOutputStream fos = new FileOutputStream(new File("D:\\TEST_POI\\third.xlsx"));
        workbook.write(fos);
        fos.close();
        workbook.close();
    }

    private static XSSFRichTextString setXssfRichTextString(Workbook workbook) {
        XSSFRichTextString richTextString = new XSSFRichTextString("富文本字符串水平删除线下划线");

        /**********************************字符串这三个字符串****************************************************/
        Font font1 = workbook.createFont();
        // 设置字体高度 - 以1pt为单位, 设置字体为60pt
        font1.setFontHeightInPoints((short)30);
        font1.setBold(true);
        // 设置字体的字符集 - ANSI字符集
        font1.setCharSet(Font.ANSI_CHARSET);
        // 设置字体颜色为深红色
        font1.setColor(Font.COLOR_RED);
        richTextString.applyFont(3, 6, font1);

        /**********************************水平删除线这五个字符串*************************************************/
        Font font2 = workbook.createFont();
        // 设置下 标字体高度为30pt
        font2.setFontHeightInPoints((short)20);
        font2.setColor(Font.COLOR_RED);
        font2.setStrikeout(true);
        richTextString.applyFont(6, 11, font2);

        /*********************************下划线这三个字符串*****************************************************/
        Font font3 = workbook.createFont();
        // 设置下 标字体高度为30pt
        font3.setFontHeightInPoints((short)30);
        font3.setColor(Font.COLOR_RED);
        font3.setItalic(true);
        // 设置字体双下划线
        font3.setUnderline(Font.U_DOUBLE);
        richTextString.applyFont(11, 14, font3);
        return richTextString;
    }
}
